package com.ww.utils;

import java.io.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import com.google.i18n.phonenumbers.NumberParseException;
import com.google.i18n.phonenumbers.PhoneNumberUtil;
import com.google.i18n.phonenumbers.Phonenumber;
import com.google.i18n.phonenumbers.geocoding.PhoneNumberOfflineGeocoder;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {

    public static void createExcel() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet0");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("ww");
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue("ww2");
//        sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 2));
        try {
            FileOutputStream fos = new FileOutputStream("/Users/wangwei/soft/ww.xls");
            wb.write(fos);
            fos.flush();
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void readExcel() {
        try {
            FileInputStream fis = new FileInputStream("/Users/wangwei/soft/export.xls");
            POIFSFileSystem poifile = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(poifile, true);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow ro = null;
            for (int i = 0; sheet.getRow(i) != null; i++) {
                ro = sheet.getRow(i);
                for (int j = 0; ro.getCell(j) != null; j++) {
                    System.out.print(ro.getCell(j) + "  ");
                }
                System.out.println();
            }
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void readExcel1() {
        try {
            DecimalFormat df = new DecimalFormat("0");
            FileInputStream fis = new FileInputStream("/Users/wangwei/soft/ex.xls");
            POIFSFileSystem poifile = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(poifile, true);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow ro = null;
            DBUtil dbUtil = new DBUtil();
            dbUtil.openConnection();

            for (int i = 0; sheet.getRow(i) != null; i++) {
                ro = sheet.getRow(i);
                String update_sql = null;
                String otaid = ro.getCell(0).toString().trim();
                String sql = "SELECT count(*) FROM product_order where otaid =" + "'" + otaid + "'";
                try {
                    ResultSet rs = dbUtil.getConn().prepareStatement(sql).executeQuery();
                    if (rs != null && rs.first()) {
                        int c = rs.getInt(1);
                        if (c == 0) {
                            System.out.println(otaid);
                        }
                    }
                    dbUtil.close(rs);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            dbUtil.close();
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    public static void writeExcel2() {
        try {
            DecimalFormat df = new DecimalFormat("0");
            FileInputStream fis = new FileInputStream("/Users/wangwei/soft/ex1.xls");
            POIFSFileSystem poifile = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(poifile, true);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow ro = null;
            DBUtil dbUtil = new DBUtil();
            dbUtil.openConnection();

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            for (int i = 1; sheet.getRow(i) != null; i++) {
                ro = sheet.getRow(i);
                DecimalFormat dfs = new DecimalFormat("0");
                String otaid = ro.getCell(5).toString(); // ro.getCell(5).toString(); // dfs.format(ro.getCell(5).getNumericCellValue());
                String cname = ro.getCell(3).toString();
                String pname = ro.getCell(2).toString();
                String contactname = ro.getCell(6).toString();
                String contactphone = ro.getCell(7).toString();
                String num = ro.getCell(8).toString();
                String starttime = sdf.format(HSSFDateUtil.getJavaDate(ro.getCell(9).getNumericCellValue()));
                String endtime = sdf.format(HSSFDateUtil.getJavaDate(ro.getCell(10).getNumericCellValue()));
                String nnum = ro.getCell(11).toString();
                String sell = ro.getCell(12).toString();
                String price = ro.getCell(13).toString();
                String submittime = sdf1.format(HSSFDateUtil.getJavaDate(ro.getCell(10).getNumericCellValue()));

                String orderid =  getOrderNumber();//ro.getCell(19).toString();

                String cid = ro.getCell(20).toString();
                String hid = ro.getCell(21).toString();
                String pid = ro.getCell(22).toString();
                String p = ro.getCell(23).toString();;

                String sql = "insert into product_order(id, otaid, orderid, cid, hid, pid, cname, platformtype, pname, roomnub, numnights, price, sell " +
                        ", starttime, stoptime, contactname, contactphone, status, otatype, submittime, billing, settlement, gathering, delflag, creatuser, creatdate, updatedate) " +
                        "values('"+getUUID()+"', '"+otaid+"', '"+orderid+"', "+cid+", "+hid+", '"+pid+"', '"+cname+"', " +
                        p +", '"+pname+"', " + num +", " + nnum +", " + price + ", "
                        + sell +", '"+ starttime +"', '" + endtime + "', " +
                        "'"+ contactname +"', '"+contactphone+"', 13, 0, '"+ submittime +"', 0, 0, 0, 0, 'bak8', '" + submittime +"', '"+submittime+"');";
                System.out.println(sql);
//                try {
//                    ResultSet rs = dbUtil.getInsertObjectIDs(sql);
//                    if (rs != null && rs.first()) {
//                        int c = rs.getInt(1);
//                        if (c == 0) {
//                            System.out.println(otaid);
//                        }
//                    }
////                    dbUtil.close(rs);
//                } catch (Exception e) {
//                    e.printStackTrace();
//                }

//                for (int j = 0; ro.getCell(j) != null; j++) {
//
//                }
            }
            dbUtil.close();
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void readExcel2() {
        try {
            DecimalFormat df = new DecimalFormat("0");
            FileInputStream fis = new FileInputStream("/Users/wangwei/soft/ex.xls");
            POIFSFileSystem poifile = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(poifile, true);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow ro = null;
            DBUtil dbUtil = new DBUtil();
            dbUtil.openConnection();

            for (int i = 0; sheet.getRow(i) != null; i++) {
                ro = sheet.getRow(i);
                String update_sql = null;
                String otaid = ro.getCell(0).toString().trim();
                String sql = "select * from product_orderlog where  status in(13, 16) and otaid = '"+otaid+"' and lastupdatedate = \n" +
                        "(select  max(lastupdatedate) as t from product_orderlog where  status in(13, 16) and otaid = '"+otaid+"');";
                try {
                    ResultSet rs = dbUtil.getConn().createStatement().executeQuery(sql);
                    while(rs.next()){
                        String valsql = "";
                        for(int j = 1; j < 42; j++){
                            if(j == 1 || j == 2 || j == 3 || j == 5 || j == 6 || j ==7  || j ==8 || j ==9
                                    || j ==16 || j == 17 || j == 18 || j == 19 || j == 21 || j == 22 || j == 23 ||j == 25 ||j == 26 || j == 27
                                    || j == 28 || j == 29 || j == 30|| j == 31|| j == 32 || j == 33 || j == 34 || j == 35|| j == 36|| j == 38|| j == 39|| j == 40|| j == 41){
                                valsql += "'" + rs.getString(j) + "',";
                            }else{
                                valsql += rs.getString(j) + ",";
                            }
                        }
                        valsql = valsql.substring(0, valsql.length() - 1);
                        String inSql = "insert into product_order(`id`,`otaid`,`orderid`,`cid`,`hid`,`pid`,`cname`,`platformtype`,`pname`,`roomnub`,`numnights`,`price`,`sell`,`hotelloss`," +
                                "`touristloss`,`starttime`,`stoptime`,`contactname`,`contactphone`,`status`," +
                                "`cancelreason`,`introduction`,`remarks`,`ismodify`,`updatedate`," +
                                "`updateuser`,`submittime`,`submituser`,`confirmuser`,`confirmtime`,`canceluser`," +
                                "`canceltime`,`financialid`,`note1`,`note2`,`note3`,`delflag`,`creatdate`,`creatuser`," +
                                "`lastupdateuser`,`lastupdatedate`, `otatype`, `sources`) values("+valsql+", 0, 'bak');";
                        System.out.println(inSql);
                    }

                    dbUtil.close(rs);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            dbUtil.close();
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void updateByExcel() {
        try {
            FileInputStream fis = new FileInputStream("/Users/wangwei/soft/ex.xls");
            POIFSFileSystem poifile = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(poifile, true);
            HSSFSheet sheet = wb.getSheetAt(0);
            for (int i = 0; sheet.getRow(i) != null; i++) {
                HSSFRow ro = sheet.getRow(i);
                String sql = "update product_order set price = "+ro.getCell(1) +" where otaid = '"+ro.getCell(0) +"';";
                System.out.println(sql);
            }
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static String getGeo(String phoneNumber) {

        PhoneNumberUtil phoneNumberUtil = PhoneNumberUtil.getInstance();
        try {
            Phonenumber.PhoneNumber swissNumberProto = phoneNumberUtil.parse(phoneNumber, "CN");
            PhoneNumberOfflineGeocoder geocoder = PhoneNumberOfflineGeocoder.getInstance();
            String rs = geocoder.getDescriptionForNumber(swissNumberProto, Locale.CHINA);
//            System.out.println(rs);
            return rs;
        } catch (NumberParseException e) {
            e.printStackTrace();
        }
        return null;
    }


    public static void main(String[] args) {
		ExcelUtils.createExcel();
//        ExcelUtils.readExcel2();
//        ExcelUtils.readExcel1();
//        ExcelUtils.writeExcel2();
//        ExcelUtils.readExcel2();

//        getGeo("13797039695");


        /*try {
            FileInputStream fis = new FileInputStream("/Users/wangwei/soft/1.xlsx");
//            Workbook wb = WorkbookFactory.create(new File("/Users/wangwei/soft/1.xlsx"));
            XSSFWorkbook wb = new XSSFWorkbook(fis);
//            Sheet sheet = wb.getSheetAt(0);
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow ro = null;
//            Row ro = null;
            for (int i = 0; sheet.getRow(i) != null; i++) {
                ro = sheet.getRow(i);
                for (int j = 0; ro.getCell(j) != null; j++) {
                    System.out.print(ro.getCell(j) + "  ");
                }
                System.out.println();
            }
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }*/

    }

    public static String getOrderNumber() throws Exception {
        long num = 0;
        SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");
        String nowdate = sdf.format(new Date());
        num = Long.parseLong(nowdate);

        //后四位随机数
        Random random = new Random();
        String result = "";
        for (int i = 0; i < 4; i++) {
            result += random.nextInt(10);
        }
        return "JY" + num + result;
    }

    public static String getUUID() throws Exception {
        UUID uuid = UUID.randomUUID();
        String orid = uuid.toString().trim();
        orid = orid.toLowerCase().replaceAll("-", "");
        return orid;
    }
}
